﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using FWPDocumentation.Common.DbTools;

namespace FWPDocumentation.Entities
{
    public class Institute
    {
        public const String TABLE_NAME = "`fvp`.`institute`";
        public const String ID = "`idInstitute`";
        public const String ID_NAME = "`Institute`";
        public int idInstitute { get; set; }
        public String name { get; set; }

        public Institute(){}

        private Institute(int id, String name)
        {
            this.idInstitute = id;
            this.name = name;
        }

        public static Institute CreateInstitute(String name)
        {
            var insertCommand = new SqlCommand("SELECT " + ID + " FROM " + TABLE_NAME + "WHERE " + ID_NAME + "='" + name+"'");
            int returnedID = insertCommand.ExecuteScalar<int>();
            if (returnedID != 0)
            {
                return null;
            }
            insertCommand = new SqlCommand(
                "INSERT INTO " + TABLE_NAME + "(" + ID_NAME + ")VALUES('" + name + "');");
            insertCommand.ExecuteNonQuery();
            insertCommand = new SqlCommand("SELECT " + ID + " FROM " + TABLE_NAME + "WHERE " + ID_NAME + "='" + name+"'");
            returnedID = insertCommand.ExecuteScalar<int>();
            return new Institute(returnedID,name);
        }

        public static List<Institute> GetInstitutes()
        {
            var insertCommand = new SqlCommand("SELECT idInstitute, Institute as name FROM " + TABLE_NAME);
            return insertCommand.ExecuteQuery<Institute>();
        }

        public override String ToString()
        {
            return name;
        }

        public List<Faculty> getFacultyes()
        {
            var insertCommand = new SqlCommand("SELECT idFaculty,Faculty as nameFaculty, Institute_idInstitute as idInstitute FROM " + Faculty.TABLE_NAME + "WHERE " + Faculty.PARENT_ID + "=" + idInstitute);
            return insertCommand.ExecuteQuery<Faculty>();
        }




    }
}
